前面講解過子查詢、集合運算、邏輯運算，關於集合的查詢方式，還剩下最精華的JOIN篇幅。JOIN也是集合運算的進階版本，以下使用文氏圖與實例說明在oracle中JOIN的使用方式，以及精簡的寫法。

JOIN分類

• JOIN是交集、聯集、差集的進階使用，主要分為以下5類，需要注意差集的JOIN寫法，要加入條件是null才能達成，否則會輸出 A or B。

↑各種JOIN的文氏圖

JOIN Example

JOIN的語法架構由JOIN與ON兩個保留字組成:
select A.col1, B.col2
from A JOIN TYPE B on A.system_key=B.system_key
where 1=1
order by 1, 2

-- example table
with A as (
    select 'Amy' name, 'CSIE' dept from dual
    union all
    select 'Oleve' name, 'Languages' dept from dual
    union all
    select 'Jake' name, 'CSIE' dept from dual
    union all
    select 'Freddy' name, 'History' dept from dual
), B as (
    select 'CSIE' dept, 'Floor A' classroom from dual
    union all
    select 'Languages' dept, 'Floor C' classroom from dual
    union all
    select 'Math' dept, 'Floor E' classroom from dual
)

• left join A-B

select A.name, B.classroom
from A Left join B on A.dept = B.dept
;
-- output
NAME   CLASSROOM
Amy    Floor A
Jake   Floor A
Oleve  Floor C
Freddy (null)     -- 沒有classroom, 還是會輸出null

• left outer JOIN A-B & B is null

select A.name, B.classroom
from A Left join B 
on A.dept = B.dept 
where 1=1
and B.dept is null
;
-- output
NAME   CLASSROOM
Freddy

• inner(natural) JOIN A & B

select A.name, B.classroom
from A inner join B 
on A.dept = B.dept 
;
-- output
NAME   CLASSROOM
Amy    Floor A
Oleve  Floor C
Jake   Floor A

• self JOIN A & A

-- 找出是同系所的同學
with class as (
    select 'Amy' name, 'CSIE' dept from dual
    union all
    select 'Oleve' name, 'Languages' dept from dual
    union all
    select 'Jake' name, 'CSIE' dept from dual
    union all
    select 'Freddy' name, 'History' dept from dual
)
select A.name, B.name, B.dept
from class A JOIN class B ON (A.dept = B.dept) and (A.name <> B.name)
;
-- output
NAME   NAME   DEPT
Amy    Jake   CSIE
Jake   Amy    CSIE

• full outer JOIN A OR B

select A.name, B.classroom
from A FULL OUTER join B 
on A.dept = B.dept
;
-- output
NAME   CLASSROOM
Amy    Floor A
Oleve  Floor C
Jake   Floor A
Freddy (NULL)
(NULL) Floor E

• cross JOIN 全部的排列組合

select A.name, B.classroom
from A CROSS join B 
;
-- output
NAME   CLASSROOM
Amy    Floor A
Oleve  Floor A
Jake   Floor A
Freddy Floor A
Amy    Floor C
Oleve  Floor C
Jake   Floor C
Freddy Floor C
Amy    Floor E
Oleve  Floor E
Jake   Floor E

JOIN的簡寫(+)

Oracle PL/SQL的(+)符號代表OUTER JOIN的意思。不過Oracle官方直接建議還是直接使用OUTER JOIN語法，不建議使用舊的(+)語法。(REF)
不過在閱讀前人的SQL還是要懂得解讀(+)，Outer join operator(+)使用範例如下:
【注意】(+)放在要被加入參考的配角身上 ex.left join主表A、副表B, (+)放在B的條件後方

select A.name, B.classroom
from A, B 
where 1=1
and A.dept = B.dept(+)  --等同於left join
;
-- output
NAME   CLASSROOM
Amy    Floor A
Jake   Floor A
Oleve  Floor C
Freddy (NULL)

Reference

• RUNOOB.COM-SQL連接
• Medium-[SQL]常用語法彙整: 集合運算指令 (JOIN) [SQL-003]
• Tech Agilist
• Oracle outer join operator (+)